use sea_orm::{
    sea_query::Alias, ActiveValue, ColumnTrait, DatabaseConnection, DbErr, DeleteResult,
    EntityOrSelect, EntityTrait, JoinType, PaginatorTrait, QueryFilter, QuerySelect, RelationTrait,
};
use sky_common::constant::status;
use sky_pojo::{
    dto::dish::{self, DishPageQueryDTO},
    entities,
    vo::PageResult,
};

// for admin
pub async fn count_by_category(db: DatabaseConnection, category_id: i64) -> Result<u64, String> {
    let result = entities::dish::Entity::find()
        .filter(entities::dish::Column::CategoryId.eq(category_id))
        .count(&db)
        .await;
    match result {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string()),
    }
}

pub async fn create(
    db: DatabaseConnection,
    model: entities::dish::ActiveModel,
) -> Result<entities::dish::Model, DbErr> {
    let result = entities::dish::Entity::insert(model)
        .exec_with_returning(&db)
        .await;

    result
}

pub async fn dish_flavor_insert_branches(
    db: DatabaseConnection,
    flavors: Vec<entities::dish_flavor::Model>,
    dish_id: i64,
) -> Result<Vec<entities::dish_flavor::Model>, DbErr> {
    let mut res_model_vec: Vec<entities::dish_flavor::Model> = Vec::new();
    for flavor in flavors {
        let name = flavor.name.clone();
        let model = entities::dish_flavor::ActiveModel {
            dish_id: ActiveValue::Set(dish_id),
            name: ActiveValue::Set(name.clone()),
            value: ActiveValue::Set(flavor.value),
            ..Default::default()
        };

        let model = entities::dish_flavor::Entity::insert(model)
            .exec_with_returning(&db)
            .await?;

        res_model_vec.push(model);
    }
    return Ok(res_model_vec);
}

pub async fn modify_by_id(
    db: DatabaseConnection,
    dish_id: i64,
    model: entities::dish::ActiveModel,
) -> Result<entities::dish::Model, DbErr> {
    entities::dish::Entity::update(model)
        .filter(entities::dish::Column::Id.eq(dish_id))
        .exec(&db)
        .await
}

pub async fn dish_flavor_modify_branches(
    db: DatabaseConnection,
    flavors: Vec<entities::dish_flavor::Model>,
) -> Result<Vec<i64>, DbErr> {
    let mut updated_flavors = Vec::new();

    for flavor in flavors {
        let model = entities::dish_flavor::ActiveModel {
            dish_id: ActiveValue::Set(flavor.dish_id),
            name: ActiveValue::Set(flavor.name),
            value: ActiveValue::Set(flavor.value),
            ..Default::default()
        };
        let updated = entities::dish_flavor::Entity::update(model)
            .exec(&db)
            .await?;
        updated_flavors.push(updated.id);
    }
    Ok(updated_flavors)
}

// dangerous
pub async fn delete_by_ids(db: DatabaseConnection, ids: Vec<i64>) -> Result<DeleteResult, DbErr> {
    entities::dish::Entity::delete_many()
        .filter(entities::dish::Column::Id.is_in(ids))
        .exec(&db)
        .await
}

pub async fn find_by_id(db: DatabaseConnection, id: i64) -> Result<entities::dish::Model, DbErr> {
    let result = entities::dish::Entity::find()
        .filter(entities::dish::Column::Id.eq(id))
        .one(&db)
        .await;

    match result {
        Ok(None) => Err(DbErr::RecordNotFound(id.to_string())),
        Ok(Some(model)) => Ok(model),
        Err(err) => Err(err),
    }
}

pub async fn find_flavors_by_dish_id(
    db: DatabaseConnection,
    id: i64,
) -> Result<Vec<entities::dish_flavor::Model>, DbErr> {
    entities::dish_flavor::Entity::find()
        .filter(entities::dish_flavor::Column::Name.is_not_null())
        .filter(entities::dish_flavor::Column::DishId.eq(id))
        .all(&db)
        .await
}
pub async fn find_by_category(
    db: DatabaseConnection,
    category_id: i64,
) -> Result<Vec<entities::dish::Model>, DbErr> {
    entities::dish::Entity::find()
        .filter(entities::dish::Column::Name.is_not_null())
        .filter(entities::dish::Column::CategoryId.eq(category_id))
        .filter(entities::dish::Column::Status.eq(status::ENABLE))
        .all(&db)
        .await
}

pub async fn query(
    db: DatabaseConnection,
    dto: DishPageQueryDTO,
) -> Result<PageResult<entities::dish::Model>, DbErr> {
    let mut stmt = entities::dish::Entity::find();

    if let Some(name) = dto.name {
        if !name.is_empty() {
            stmt = stmt.filter(entities::dish::Column::Name.contains(&name));
        }
    }

    // 条件过滤
    if let Some(category_id) = dto.category_id {
        stmt.clone()
            .filter(entities::dish::Column::Id.eq(category_id));
    }

    if let Some(status) = dto.status {
        stmt.clone().filter(entities::dish::Column::Id.eq(status));
    }

    // 总记录数
    let total: u64 = stmt.clone().count(&db).await?;

    // 分页数据
    let dish_vec = stmt
        .paginate(&db, dto.page_size.try_into().unwrap())
        .fetch_page((dto.page - 1).try_into().unwrap())
        .await?;

    Ok(PageResult {
        total,
        records: dish_vec,
    })
}

pub async fn get_by_setmeal_id(
    db: DatabaseConnection,
    setmeal_id: i64,
) -> Result<Vec<entities::dish::Model>, DbErr> {
    // select a.* from dish a left join setmeal_dish b on a.id = b.dish_id where b.setmeal_id = #{setmealId}

    entities::dish::Entity::find()
        .join(
            JoinType::LeftJoin,
            entities::setmeal_dish::Relation::Dish.def(),
        )
        .filter(entities::setmeal_dish::Column::SetmealId.eq(setmeal_id))
        .all(&db)
        .await
}

pub struct DishFlavorMapper {
    pub name: Option<String>,
    pub category_id: i64,
    pub status: i32,
}

pub async fn list(
    db: DatabaseConnection,
    dish: DishFlavorMapper,
) -> Result<Vec<entities::dish::Model>, DbErr> {
    let mut stmt = entities::dish::Entity::find();
    if dish.name.is_some() {
        stmt = stmt.filter(entities::dish::Column::Name.contains(dish.name.unwrap()));
    }
    stmt.filter(entities::dish::Column::CategoryId.eq(dish.category_id))
        .filter(entities::dish::Column::Status.eq(dish.status))
        .all(&db)
        .await
}
